package leave.dataaccess.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import leave.dataaccess.LeaveTypeDAO;
import leave.exception.DataAccessException;
import leave.model.LeaveType;
import leave.model.impl.mysql.LeaveTypeImpl;

public class LeaveTypeDAOImpl implements LeaveTypeDAO {

	private Connection ConnectionFactory;
	
	public LeaveTypeDAOImpl(Connection connectionFactory) {
		super();
		ConnectionFactory = connectionFactory;
	}

	@Override
	public LeaveType[] getAllLeaveTypes() throws DataAccessException {
		// TODO Auto-generated method stub
		String queryCommand = "select * from leave_type;";
		ArrayList<LeaveType> LTList = new ArrayList<LeaveType>();
		Statement st = null;
		try {
			st = ConnectionFactory.createStatement();
			ResultSet rs = st.executeQuery(queryCommand);
			while (rs.next()){
				LeaveType LTEntry = new LeaveTypeImpl();
				LTEntry.setID(rs.getInt("leave_type_id"));
				LTEntry.setName(rs.getString("leave_name"));
				LTEntry.setDescription(rs.getString("description"));
				LTList.add(LTEntry);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
		return (LeaveType[]) LTList.toArray();
	}

	@Override
	public LeaveType createNewLeaveType() {
		return new LeaveTypeImpl();
	}

	@Override
	public void addLeaveType(LeaveType lt) throws DataAccessException {
		String addCommand = "Insert into leave_type" +
							"values (?, ?, ?);";
		PreparedStatement st = null;
		try {
			st = (PreparedStatement) ConnectionFactory.prepareStatement(addCommand);
			st.setInt(1, lt.getID());
			st.setString(2, lt.getName());
			st.setString(3, lt.getDescription());
			
			st.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
		
	}

	@Override
	public void updateLeaveType(LeaveType lt) throws DataAccessException {
		String updateCommand = "update leave_type" +
							   "set leave_name = ?," +
							   "description = ?" +
							   "where leave_id = ?;";
		PreparedStatement st = null;
		try {
			st = (PreparedStatement) ConnectionFactory.prepareStatement(updateCommand);
			st.setString(1, lt.getName());
			st.setString(2, lt.getDescription());
			st.setInt(3, lt.getID());
			
			st.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}

	@Override
	public void deleteLeaveType(LeaveType lt) throws DataAccessException {
		String deleteCommand = "delete from leave_type" +
							   "where leave_id = ?;";
		PreparedStatement st = null;
		try {
			st = (PreparedStatement) ConnectionFactory.prepareStatement(deleteCommand);
			st.setInt(1, lt.getID());
			
			st.executeUpdate();
			
			lt = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}

}
